CREATE PROC [dbo].[asi_GenerateContactSalutation] (@salutationKey uniqueidentifier)
AS
BEGIN

SET NOCOUNT ON

DECLARE @sql nvarchar(2000)
DECLARE @contactKey uniqueidentifier
DECLARE @IndividualFormula nvarchar(2000)
DECLARE @InstituteFormula nvarchar(2000)

DECLARE @calcFormula nvarchar(2000)

DECLARE @tempKey uniqueidentifier
DECLARE @tempValue nvarchar(1000)


SELECT @IndividualFormula = IndividualFormula, @InstituteFormula = InstituteFormula
    FROM SalutationRef
    WHERE SalutationRef.SalutationKey=@salutationKey AND AutoCreateFlag = 1

IF @@ROWCOUNT = 0
    RETURN

DECLARE theContactCursor CURSOR FAST_FORWARD FOR
    SELECT ContactKey
        FROM vBoContact

OPEN theContactCursor
FETCH NEXT FROM theContactCursor INTO @contactKey

WHILE @@FETCH_STATUS = 0 -- spin through Contact entries
BEGIN

    SET @tempKey = newid()

    SELECT @sql =  
        CASE
        WHEN IsInstitute = 1 THEN
            ' asi_ProcessFormulaOutput '
                + '''' + convert (nvarchar(100), @tempKey) + ''', '
                + '''' + @InstituteFormula + ''', '
                + '''Institute'', ''ContactKey'', '
                + '''' + convert (nvarchar(50), @contactKey)
                + ''''

        WHEN IsInstitute = 0 THEN
            ' asi_ProcessFormulaOutput '
                + '''' + convert (nvarchar(100), @tempKey) + ''', '
                + '''' + @IndividualFormula + ''', '
                + '''Individual'', ''ContactKey'', '
                + '''' + convert (nvarchar(50), @contactKey)
                + ''''
        END
    FROM ContactMain WHERE ContactKey = @contactKey

    EXEC (@sql)

    SELECT @tempValue = tempValue
        FROM tempFormula
        WHERE tempKey = @tempKey

    IF @tempValue IS NOT NULL AND datalength (@tempValue) > 0
    BEGIN
        IF not exists (SELECT 1 FROM vBoContactSalutation
                WHERE ContactKey = @contactKey and SalutationKey = @salutationKey)
        BEGIN
            INSERT vBoContactSalutation
                (ContactSalutationKey, IsOverridden, SalutationText, ContactKey, SalutationKey, IsDeletable)
            VALUES (@tempKey, 0, @tempValue, @contactKey, @salutationKey, 1)
        END
        ELSE
        BEGIN
            UPDATE vBoContactSalutation set IsDeletable = 1 WHERE ContactKey = @contactKey and SalutationKey = @salutationKey
        END
    END

    DELETE FROM tempFormula WHERE tempKey = @tempKey

    FETCH NEXT FROM theContactCursor INTO @contactKey
END  -- spin through Contact entries

CLOSE theContactCursor
deallocate theContactCursor

UPDATE SalutationRef SET NeedsGenerationFlag = 0 WHERE SalutationKey = @salutationKey

SET NOCOUNT OFF

END

GO